package edu.uwm.universitydb.tests;

public class Query1 extends PerformanceTest {


	@Override
	public String getName() {
		return "Everybody involved in highest funded projects";
	}

	@Override
	public String getQuery() {
		return "SELECT stu.name " +
				"FROM grad_student stu, project pr, supervises sup " +
				"WHERE stu.grad_ssn = sup.grad_ssn " +
				"AND pr.budget = (SELECT max(pr1.budget) " +
				"				 FROM project pr1) " +
				"GROUP BY stu.grad_ssn " +
				"UNION ( " +
				"	SELECT prof.name " +
				"	FROM project pr, supervises sup, professor prof " +
				"	WHERE prof.prof_ssn = sup.prof_ssn " +
				"	AND pr.budget = (SELECT max(pr2.budget) " +
				"					 FROM project pr2) " +
				"	GROUP BY prof.prof_ssn) " +
				"UNION ( " +
				"	SELECT prof.name " +
				"	FROM project pr, co_investigate co, professor prof " +
				"	WHERE prof.prof_ssn = co.prof_ssn " +
				"	AND pr.budget = (SELECT max(pr3.budget) " +
				"					 FROM project pr3) " +
				"	GROUP BY prof.prof_ssn);";
	}

	@Override
	public String[] getAddIndex() {
		return new String[] {
				"create index budget_index on project (budget) using btree;",
				"create index student_index on grad_student (grad_ssn) using hash;",
				"create index prof_index on professor (prof_ssn) using hash;",
			};
	}

	@Override
	public String[] getDropIndex() {
		return new String[] {
				"drop index budget_index on project",
				"drop index student_index on grad_student",
				"drop index prof_index on professor"
		};
	}
}
